/********************************************************************************
 *      Copyright:  (C) 2020 makun<1394987689@qq.com>
 *                  All rights reserved.
 *
 *       Filename:  database.c
 *    Description:  This head file 
 *
 *        Version:  1.0.0(2020年04月28日)
 *         Author:  makun <1394987689@qq.com>
 *      ChangeLog:  1, Release initial version on "2020年04月28日 22时13分32秒"
 *                 
 ********************************************************************************/
#include "database.h"


//initial database
int  database_init(sqlite3 **db)
{
    char                    *zErrmsg=NULL;
    if(sqlite3_open("data.db", db)!=SQLITE_OK)//创建或打开数据库
    {
        printf("create dataname failure:%s\n",sqlite3_errmsg(*db));
        return -1;
    }
    printf("sqlite open successfuly\n");

    //判断数据库的表是否存在，如果不存在则创建表
    if(sqlite3_exec(*db,"create table if not exists temperature (id INTEGER PRIMARY KEY AUTOINCREMENT,sn varchar,temp varchar,datatime varchar);",NULL,NULL,&zErrmsg)!=SQLITE_OK)
    {
        printf("Create failed:%s\n",zErrmsg);
        return -2;
    }

    printf("table create successfuly\n");
    return 0;
}


//save data after send failure
int save_into_db(sqlite3 *db, tlv_send_data *send_db)
{
    char insert[512];
    int  i=0;
    char *zErrmsg=NULL;
    memset(insert, 0, sizeof(insert));
   
    dump_buf(send_db->sn,12);
    snprintf(insert, sizeof(insert), "insert into temperature values(NULL,'%s','%s','%s');",send_db->sn,send_db->temp,send_db->datatime);
    if((sqlite3_exec(db, insert, NULL, NULL,&zErrmsg) != SQLITE_OK ))
    {
        printf("sql error:%s\n",zErrmsg);
        sqlite3_free(zErrmsg);
    }
    printf("insert and write data successfuly\n");
    return 0;
}


//Read data from  database
int  read_from_db(sqlite3 *db,tlv_send_data *tlv_data)
{
    char *zErrmsg=NULL;
    int rows=0,cols=0;
    char  **dbresult;
    int   rc;
    char *sql2="SELECT *FROM TEMPERATURE LIMIT 1 ";

    rc=sqlite3_exec(db, sql2,callback,(void *)tlv_data,&zErrmsg);
    if(rc !=SQLITE_OK)
    {
        printf("sql error:%s\n", zErrmsg);
        sqlite3_free(zErrmsg);
        return -1;
    }
    else
    {
        printf("select from temperature successfuly\n");
    }
    return  0;  
}
static int callback(void *ptr, int argc, char **argv, char **azCoName)
{
    int i;
    tlv_send_data *data =(tlv_send_data *)ptr;    
    for(i=0;i <argc; i++)
    {
           //printf("%s = %s\n",azCoName[i],argv[i]);
        if(!strcmp("id", azCoName[i]))
        {
            data->id = atoi(argv[i]);
        }
        else if(!strcmp("sn",azCoName[i]))
        {
            dump_buf(data->sn,12);
            strcpy(data->sn,argv[i]);
        }
        else if(!strcmp("temp",azCoName[i]))
        {
            strcpy(data->temp,argv[i]);
        }
        else if(!strcmp("datatime",azCoName[i]))
        {
            strcpy(data->datatime,argv[i]);
        }
    }
    printf("\n");
    return 0;
}
 
//get the number of records in the database
int get_database_count(sqlite3 *db)
{
    char  *sql;
    char   *zErrmsg=NULL;
    int    rc= -1;
    int    num=0;
    sql = "SELECT count(*) from temperature";
    rc = sqlite3_exec(db, sql,get_callback,(void *)&num, &zErrmsg);
    if( rc != SQLITE_OK )
    {
        printf("SQL error: %s\n", zErrmsg);
        sqlite3_free(zErrmsg);
        return -1;
    }
    return num;
}
static int get_callback(void *data, int argc, char **argv, char **azCoName)
{
    int *count = (int *)data;
    if(argv[0]!=NULL)
    {
        *count = atoi(argv[0]);
    }
    else
    {
        *count = 0;
    }
    return 0;
}

//delete a record from database
int delete_db(sqlite3 *db)
{
    char *zErrmsg =NULL;
    char *sql3="DELETE FROM temperature where id =(SELECT id from temperature limit 1)";         
    if((sqlite3_exec(db, sql3, NULL, NULL,&zErrmsg) != SQLITE_OK ))
    {   
        printf("sql error:%s\n",zErrmsg);
        sqlite3_free(zErrmsg);
    } 
    printf("delect from temperature successfuly\n");
    return 0;
}
